<?php
/**
 * Created by PhpStorm.
 * User: 沁塵
 * Date: 2019/4/20
 * Time: 19:57
 */

namespace app\api\controller\v1;

use app\api\model\Account;
use app\api\model\LinUser;
use app\api\model\TbAccount;
use app\api\model\TbAudio;
use app\api\model\TbLog;
use app\api\model\TbProduct;
use app\api\service\token\LoginToken;
use app\lib\qiniu\Qiniu;
use think\Db;
use think\Exception;
use think\Request;

require_once '../vendor/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once '../vendor/PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
use PHPExcel;
use PHPExcel_IOFactory;

class Tb
{
    public function importProduct(Request $request)
    {
        $file = $request->file('file');

        $filename  = $file->getInfo()['tmp_name'];
        //设置excel格式
        $reader = \PHPExcel_IOFactory::createReader('Excel2007');
        //载⼊excel⽂件
        $excel = $reader->load($filename);
        //读取第⼀张表
        $sheet = $excel->getSheet(0);
        //获取总⾏数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = 'L';
        $datas = []; //数组形式获取表格数据
        $head = [];

        for($row=1;$row<=$row_num;$row++)
        {
            for($col='A';$col<=$col_num;$col++) {
                if ($row == 1) {
                    $head[] = $sheet->getCell($col."1")->getValue();
                } else {
                    $datas[$row-2][] = $sheet->getCell($col.$row)->getValue();
                }
            }
        }
        $array2 = ["商品名称", "商品链接", "一口价", "直播价", "佣金率", "佣金截止时间", "优惠券", "优惠券时间", "卖点", "标签", "类目", "商品频率"];
        if (array_diff_assoc($head, $array2) != NULL) {
            return writeJson(200,"表结构不正确",'完成！');
        }
        $save_data = [];
        $num = 0;
        $event = new TbProduct();
        DB::startTrans();
        try {
            for($row=0;$row<count($datas);$row++)
            {
                $val = [];

                $val["pname"] = $datas[$row][0];
                $val["link"] = $datas[$row][1];
                $val["price"] = $datas[$row][2];
                $val["sale_price"] = $datas[$row][3];
                $val["commission"] = $datas[$row][4];
                $val["deadline"] = $datas[$row][5];
                $val["coupon"] = $datas[$row][6];
                $val["coupon_time"] = $datas[$row][7];
                $val["point"] = $datas[$row][8];
                $val["tag"] = $datas[$row][9];
                $val["category"] = $datas[$row][10];
                $val["pro_rate"] = $datas[$row][11];

                // 使用 parse_url 解析 URL，获取查询字符串部分
                $parsedUrl = parse_url($val["link"]);
                $queryString = $parsedUrl['query'];
                // 使用 parse_str 解析查询字符串为关联数组
                parse_str($queryString, $queryParams);
                $val["pid"] = $queryParams['id'];


                //查重
                $info = $event->where("pid", $val["pid"])->findOrEmpty();
                if (!$info->isEmpty()) {
                    $flag = 0;
                    //更新
                    if ($info["commission"] != $val["commission"]) {
                        $info->commission = $val["commission"];
                        $info->commission_change = intval($info["commission_change"]) + 1;
                        $flag = 1;
                    }
                    if ($info["pname"] != $val["pname"]) {
                        $info->pname = $val["pname"];
                        $flag = 1;
                    }
                    if ($flag == 1) {
                        $info->save();
                        $num += 1;
                    }
                } else {
                    #写入数据库
                    $save_data[] = $val;
                }

            }
            $num1 = 0;
            if (count($save_data) > 0) {
                $res = $event->saveAll($save_data);
                $num1 = count($res);
            }

        // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'导表失败！');

        }
        return writeJson(200,true,"增：{$num1}，更：{$num}");
    }

    public function list(Request $request)
    {
        $params = $request->post();

        $pageSize = $params['pageSize'] ?? 50;
        $event = new TbProduct();

        $query = $event->field("*");

        if (!empty($params["pid"])) {
            $query = $query->whereLike("pid", "%".$params["pid"]."%");
        }
        if (!empty($params["pname"])) {
            $query = $query->whereLike("pname", "%".$params["pname"]."%");
        }
        if (!empty($params["category"])) {
            $query = $query->where("category", $params["category"]);
        }
        if (!empty($params["pro_rate"])) {
            $query = $query->where("pro_rate", $params["pro_rate"]);
        }
        if (!empty($params["min_commission"])) {
            $query = $query->where("commission", ">=", $params["min_commission"]);
        }
        if (!empty($params["max_commission"])) {
            $query = $query->where("commission", "<=", $params["max_commission"]);
        }
        $list = $query->order("id desc")->paginate($pageSize);
        return writeJson(200,$list,'数据列表！');
    }

    public function getCategory()
    {
        $list = TbProduct::field("category")->distinct(true)->select()->toArray();
        $category = [];
        if (!empty($list)) {
            foreach ($list as $li) {
                $category[] = $li["category"];
            }
        }
        return writeJson(200,$category,'数据列表！');

    }

    public function importAccount(Request $request)
    {
        $file = $request->file('file');

        $filename  = $file->getInfo()['tmp_name'];
        //设置excel格式
        $reader = \PHPExcel_IOFactory::createReader('Excel2007');
        //载⼊excel⽂件
        $excel = $reader->load($filename);
        //读取第⼀张表
        $sheet = $excel->getSheet(0);
        //获取总⾏数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = 'I';
        $datas = []; //数组形式获取表格数据
        $head = [];

        for($row=1;$row<=$row_num;$row++)
        {
            for($col='A';$col<=$col_num;$col++) {
                if ($row == 1) {
                    $head[] = $sheet->getCell($col."1")->getValue();
                } else {
                    $datas[$row-2][] = $sheet->getCell($col.$row)->getValue();
                }
            }
        }
        $array2 = ["组长名称", "账号ID", "账号名称", "账号主页", "类目", "副类目", "主播", "短视频主类目", "短视频副类目"];
        if (array_diff_assoc($head, $array2) != NULL) {
            return writeJson(200,"表结构不正确",'完成！');
        }
        $save_data = [];
        $num = 0;
        $event = new TbAccount();
        DB::startTrans();
        try {
            for($row=0;$row<count($datas);$row++)
            {
                $val = [];

                $val["leader"] = $datas[$row][0];
                $val["account_id"] = $datas[$row][1];
                $val["username"] = $datas[$row][2];
                $val["link"] = $datas[$row][3];
                $val["category"] = $datas[$row][4];
                $val["fu_category"] = $datas[$row][5];
                $val["anchor"] = $datas[$row][6];
                $val["main_category"] = $datas[$row][7];
                $val["sub_category"] = $datas[$row][8];

                //查重
                $info = $event->where("account_id", $val["account_id"])->findOrEmpty();
                if (!$info->isEmpty()) {
                    $flag = 0;
                    //更新
                    if ($info["username"] != $val["username"]) {
                        $info->username = $val["username"];
                        $flag = 1;
                    }
                    if ($info["category"] != $val["category"]) {
                        $info->category = $val["category"];
                        $flag = 1;
                    }
                    if ($info["fu_category"] != $val["fu_category"]) {
                        $info->fu_category = $val["fu_category"];
                        $flag = 1;
                    }
                    if ($info["anchor"] != $val["anchor"]) {
                        $info->anchor = $val["anchor"];
                        $flag = 1;
                    }
                    if ($info["main_category"] != $val["main_category"]) {
                        $info->main_category = $val["main_category"];
                        $flag = 1;
                    }
                    if ($info["sub_category"] != $val["sub_category"]) {
                        $info->sub_category = $val["sub_category"];
                        $flag = 1;
                    }
                    if ($flag == 1) {
                        $info->save();
                        $num += 1;
                    }
                } else {
                    #写入数据库
                    $save_data[] = $val;
                }

            }
            $num1 = 0;
            if (count($save_data) > 0) {
                $res = $event->saveAll($save_data);
                $num1 = count($res);
            }

            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'导表失败！');

        }
        return writeJson(200,true,"增：{$num1}，更：{$num}");
    }

    public function importOrder(Request $request)
    {
        $file = $request->file('file');

        $filename  = $file->getInfo()['tmp_name'];
        //设置excel格式
        $reader = \PHPExcel_IOFactory::createReader('Excel2007');
        //载⼊excel⽂件
        $excel = $reader->load($filename);
        //读取第⼀张表
        $sheet = $excel->getSheet(0);
        //获取总⾏数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = 'Q';
        $datas = []; //数组形式获取表格数据
        $head = [];

        for($row=1;$row<=$row_num;$row++)
        {
            for($col='A';$col<=$col_num;$col++) {
                if ($row == 1) {
                    if ($col == "D" || $col == "E" || $col == "Q") {
                        $head[] = $sheet->getCell($col."1")->getValue();
                    }
                } else {
                    if ($col == "D" || $col == "E" || $col == "Q") {
                        $datas[$row-2][] = $sheet->getCell($col.$row)->getValue();
                    }
                }
            }
        }
//        dump($datas);
//        $array2 = ["淘宝主订单号", "淘宝子订单号", "店铺名称", "商品名称", "商品ID", "达人昵称", "机构昵称", "服务商类型", "服务商昵称", "购买数量", "订单状态", "订单创建时间", "订单支付时间", "订单确认收货时间", "订单支付金额", "订单成交金额", "商品佣金比例"];
        $array2 = ["商品名称", "商品ID", "商品佣金比例"];
        if (array_diff_assoc($head, $array2) != NULL) {
            return writeJson(200,"表结构不正确",'完成！');
        }
        $num = 0;
        $event = new TbProduct();
//        DB::startTrans();
//        try {
        $data = [];
        for($row=0;$row<count($datas);$row++)
        {
            $val = [];

            $val["pname"] = $datas[$row][0];
            $val["pid"] = trim($datas[$row][1]);
            $val["commission"] = $datas[$row][2];

            if ($data == []) {
                $data[] = $val;
            } else {
                $flag = 0;
                foreach ($data as $da) {
                    if ($da["pid"] == $val["pid"]) {
                        $flag = 1;
                        break;
                    }
                }
                if ($flag == 0) {
                    $data[] = $val;
                }
            }

        }
        if (count($data) > 0) {
            foreach ($data as $da) {
                $info = $event->where("pid", $da["pid"])->findOrEmpty();
                if (!$info->isEmpty()) {
                    $up_flag = 0;
                    //更新
                    if ($info["pname"] != $da["pname"]) {
                        $info->pname = $da["pname"];
                        $up_flag = 1;
                    }
                    if ($info["commission"] != $da["commission"]) {
                        $info->commission = $da["commission"];
                        $info->commission_change = intval($info["commission_change"]) + 1;
                        $up_flag = 1;
                    }
                    if ($up_flag == 1) {
                        $info->save();
                        $num += 1;
                    }
                }
            }
        }

            // 提交事务
//            Db::commit();
//        }catch (Exception $e) {
//            // 回滚事务
//            Db::rollback();
//            return writeJson(200,0,'导表失败！');
//
//        }
        return writeJson(200,true,"更：{$num}");
    }

    public function upProduct(Request $request)
    {
        $params = $request->post();
        $data = $params["data"];
        DB::startTrans();
        try {
            if (count($data) > 0) {
                $event = new TbProduct();
                $num = 0;
                $save_data = [];
                foreach ($data as $val) {

                    //查重
                    $info = $event->where("pid", $val["pid"])->findOrEmpty();
                    if (!$info->isEmpty()) {
                        $flag = 0;
                        //更新
                        if (isset($val["commission"]) && $info["commission"] != $val["commission"]) {
                            $info->commission = $val["commission"];
                            $info->commission_change = intval($info["commission_change"]) + 1;
                            $flag = 1;
                        }
                        if (isset($val["pname"]) && $info["pname"] != $val["pname"]) {
                            $info->pname = $val["pname"];
                            $flag = 1;
                        }
                        if (isset($val["pro_rate"]) && $info["pro_rate"] != $val["pro_rate"]) {
                            $info->pro_rate = $val["pro_rate"];
                            $flag = 1;
                        }
                        if ($flag == 1) {
                            $info->save();
                            $num += 1;
                        }
                    } else {
                        #写入数据库
                        $save_data[] = $val;
                    }
                }
                $num1 = 0;
                if (count($save_data) > 0) {
                    $res = $event->saveAll($save_data);
                    $num1 = count($res);
                }
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'失败！');

        }
        return writeJson(200,true,"增：{$num1}，更：{$num}");
    }

    public function upOrder(Request $request)
    {
        $params = $request->post();
        $data = $params["data"];
        DB::startTrans();
        try {
            if (count($data) > 0) {
                $event = new TbProduct();
                $num = 0;
                foreach ($data as $val) {

                    //查重
                    $info = $event->where("pid", $val["pid"])->findOrEmpty();
                    if (!$info->isEmpty()) {
                        $flag = 0;
                        //更新
                        if ($info["commission"] != $val["commission"]) {
                            $info->commission = $val["commission"];
                            $info->commission_change = intval($info["commission_change"]) + 1;
                            $flag = 1;
                        }
                        if ($info["pname"] != $val["pname"]) {
                            $info->pname = $val["pname"];
                            $flag = 1;
                        }
                        if ($flag == 1) {
                            $info->save();
                            $num += 1;
                        }
                    }
                }
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'失败！');

        }
        return writeJson(200,true,"更：{$num}");
    }

    public function getAccount(Request $request)
    {
        $params = $request->get();
        $pageSize = $params['pageSize'] ?? 50;

        $query = TbAccount::field("*");
        if(!empty($params["leader"])){
            $query = $query->where('leader','like','%'.$params["leader"].'%');
        }
        if(!empty($params["username"])){
            $query = $query->where('username','like','%'.$params["username"].'%');
        }

        $result = $query->paginate($pageSize);
        return writeJson(200, $result, '商品列表');
    }

    public function clearAccount()
    {
        $event = new TbAccount();
        $res = $event->where("id", ">", 0)->delete();
        return writeJson(200, $res, '删除成功');
    }

    public function clearProduct()
    {
        $event = new TbProduct();
        $res = $event->where("id", ">", 0)->delete();
        return writeJson(200, $res, '删除成功');
    }

    public function upAccount(Request $request)
    {
        $params = $request->post();
        $data = $params["data"];
        DB::startTrans();
        try {
            if (count($data) > 0) {
                $event = new TbAccount();
                $num = 0;
                $num1 = 0;
                foreach ($data as $val) {

                    //查重
                    $info = $event->where("account_id", $val["account_id"])->findOrEmpty();
                    if (!$info->isEmpty()) {
                        //更新
                            $event->where("id", $info["id"])->update($val);
                            $num += 1;
                    } else {
                        $event->create($val);
                        $num1 += 1;
                    }
                }
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'失败！');

        }
        return writeJson(200,true,"增：{$num1}，更：{$num}");

    }


    public function upProRate(Request $request)
    {
        $params = $request->post();
        $data = $params["data"];
        DB::startTrans();
        try {
            if (count($data) > 0) {
                $event = new TbProduct();
                $num = 0;
                foreach ($data as $val) {
                    //查重
                    $info = $event->where("pid", $val["pid"])->findOrEmpty();
                    if (!$info->isEmpty()) {
                        //更新
                        $info->pro_rate = $val["pro_rate"];
                        $info->save();
                        $num += 1;
                    }
                }
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,0,'失败！');

        }
        return writeJson(200,true,"更：{$num}");
    }


    public function insertLog(Request $request)
    {
        $params = $request->post();
        $res =  TbLog::create($params);
        return writeJson(200,$res,"成功");
    }

    public function uploadPicUrl(Request $request)
    {
        $params = $request->post();
        $key = $params["key"]??null;
        $qiniu = new Qiniu($params["bucketName"]);
        $res = $qiniu->foreverUrlUpload($params["url"], $key);
        return  writeJson(200, $res, '七牛上传');

    }

    public function getCurAudio(Request $request)
    {
        $params = $request->get();
        // 创建当前时间对象并减去5分钟
        $five_min_ago = date("Y-m-d H:i:s", time() - 300);
        $list = [];
        //获取app_id 获取5分钟内最新的记录
        $info = TbLog::where("app_id", $params["app_id"])->order("id desc")->findOrEmpty();
        if (!$info->isEmpty()) {
            $five_min_ago_ts = strtotime($five_min_ago);
            $create_time_ts = strtotime($info["create_time"]);
            if ($create_time_ts > $five_min_ago_ts) {
                //创建时间在5分钟内
                $list = TbAudio::where("pid", $info["pid"])->orderRand()->findOrEmpty();
            }
        }
        //随机一个通用话术
        if (empty($list)) {
            $list = TbAudio::where("pid", "common")->orderRand()->findOrEmpty();
        }
        return writeJson(200,$list,"成功");

    }

    public function getAudios(Request $request)
    {
        $params = $request->post();
        $list = TbAudio::whereIn("pid", $params["pids"])->select()->toArray();
        $result = [];
        foreach ($params["pids"] as $val) {
            $item = [];
            $item["pid"] = $val;
            $item["audio"] = [];
            foreach ($list as $li) {
                if ($li["pid"] == $val) {
                    $item["audio"][] = $li["audio"];
                }
            }
            $result[] = $item;
        }
        return writeJson(200,$result,"成功");

    }

    public function addAudio(Request $request)
    {
        $params = $request->post();
        $res = TbAudio::create($params);
        return writeJson(200,$res,"成功");

    }

    public function getAudio()
    {
        $info = TbAudio::whereNull("audio")->findOrEmpty();
        if (!$info->isEmpty()) {
            $info->audio = "ing";
            $info->save();
        }
        return writeJson(200,$info,"成功");

    }

    public function setAudio(Request $request)
    {
        $params = $request->post();
        $info = TbAudio::where("id", $params["id"])->findOrEmpty();
        if (!$info->isEmpty()) {
            $info->audio = $params["audio"];
            $res = $info->save();
        }
        return writeJson(200,$res,"成功");

    }
}